import pymysql

from scrapy_fiction import settings

class MySQLHelper:

    def __init__(self):
        self.host = settings.MYSQL_HOST
        self.port = settings.MYSQL_PORT
        self.db = settings.MYSQL_DB
        self.user = settings.MYSQL_USER
        self.password = settings.MYSQL_PASSWD
        self.charset = "utf8"

    #数据库连接配置
    def connect(self):
        self.conn = pymysql.connect(host=self.host,port=self.port,db=self.db,user=self.user,password=self.password,
                                    charset=self.charset,cursorclass=pymysql.cursors.DictCursor)
        #创建游标
        self.cursor = self.conn.cursor()

    def close(self):
        #优先关闭游标
        self.cursor.close()
        #关闭数据库连接
        self.conn.close()

    def insert(self,sql,params=()):
        return self._edit(sql,params)

    def update(self,sql,params=()):
        return self._edit(sql,params)

    def delete(self,sql,params=()):
        return self._edit(sql,params)

    def _edit(self,sql,params):
        count = 0
        try:
            self.connect()

            count = self.cursor.execute(sql,params)

            self.conn.commit()

            self.close()

        except Exception as e:
            raise e
            print(e)
        return count

    def get_one(self,sql,params=()):
        result = None
        try:
            self.connect()
            self.cursor.execute(sql,params)
            result = self.cursor.fetchone()

            self.close()

        except Exception as e:
            raise e
            print(e)

        return result

    def get_all(self,sql,params):
        resultList = []
        try:
            self.connect()
            self.cursor.execute(sql,params)
            resultList = self.cursor.fetchall()

            self.close()

        except Exception as e:
            raise e
            print(e)

        return resultList